﻿

CREATE PROCEDURE [dbo].[proc_L_Reimbursement_Create]
	(
		@Summary NVARCHAR(100),
		@CompanyId INT ,
		@CompanyName nvarchar(100),
		--@DepartmentId int,
		--@DepartmentName nvarchar(100),
		@ENameId VARCHAR(10),
		@EName nvarchar(10),
		@Memo NVARCHAR(1000),
		@userid varchar(10),
		@username NVARCHAR(10),
		@SHPerson VARCHAR(10),
		@SHPersonName NVARCHAR(10),
		@PZPersonName VARCHAR(10),
		@PZPerson	 NVARCHAR(10),
		@BXJE FLOAT,
		@BXflag INT,
		@PaymentTypeId int,
		@PaymentTypeName nvarchar(50),
		@BankId int,
		@BankName nvarchar(50)
		
	)
AS
BEGIN
	
BEGIN TRANSACTION--开始事务
DECLARE @errorSun INT --定义错误计数器
SET @errorSun=0 --没错为0
                --
  declare @TempSca table(id int Identity(1,1),d_str int )
  declare @departmentId int,@departmentName varchar(50)
INSERT INTO @TempSca(d_str) SELECT id FROM L_Reimbursement_Assets lra WHERE lra.itemid IN 
					(SELECT lri.id
					   FROM L_Reimbursement_Item lri WHERE  userid=@userid  AND lri.Pid=0)
  
  Select @departmentId=Id,@departmentName=DName From Department 
   Where Id=(Select min(DepartmentId) from Employee where UserName =@ENameId)
                
	INSERT INTO [L_Reimbursement]
           ([Summary]
           ,[CompanyId]
           ,[CompanyName]
           ,[DepartmentId]
           ,[DepartmentName]
           ,[ENameId]
           ,[EName]
           ,[PZflag]
           ,[PZPerson]
           ,[PZPersonName]
           ,[PZflag1]
           ,[PZPerson1]
           ,[PZPersonName1]
           ,[SHflag]
           ,[SHPerson]
           ,[SHPersonName]
           ,[Memo]
           ,[lrrq]
           ,[userid]
           ,[username]         
           ,[MONEY]
           ,BXJE
           ,BXflag
           ,PaymentTypeId, PaymentTypeName, BankId, BankName)
     SELECT 
           @Summary
           ,@CompanyId
           ,@CompanyName
           ,@departmentId
           ,@departmentName
           ,@ENameId
           ,@EName
           ,0
           ,@PZPerson
           ,@PZPersonName
           ,0
           ,''
           ,''
           ,0
           ,@SHPerson
           ,@SHPersonName
           ,@Memo
           ,GETDATE()
           ,@userid
           ,@username
           , SUM([Prices])
           ,SUM([Prices])
           ,@BXflag
           ,@PaymentTypeId
           ,@PaymentTypeName
           ,@BankId
           ,@BankName
            FROM [L_Reimbursement_Item]  WHERE userid=@userid  AND pid=0
           
  if @@ROWCOUNT<1
	begin
		set @errorSun=@errorSun+1		
		goto e;
	END

		
DECLARE @pid INT 
--刚插入的报销记录的ID
SET @pid= @@identity

--新录的事由更新PID为刚插入的报销记录的ID
UPDATE [L_Reimbursement_Item]
   SET [Pid] = @pid
WHERE userid=@userid AND pid=0


	if @@ROWCOUNT<1
		begin
			set @errorSun=@errorSun+1
			goto e;
		END



Insert Into Finance_Outlay
	(
		CreateDate,
		TradeDate,
		Summary,
		CateOneId,
		CateOneName,
		CateTwoId,
		CateTwoName,
		SubjectId,
		SubjectName,
		PaymentTypeId,
		PaymentTypeName,
		BankId,
		BankName,
		OutlayReason,
		OutlayPrice,
		Agent,
		AgentName,
		PZPerson,
		PZPersonName,
		SHPerson,
		SHPersonName,
		UserName,
		EName,
		Description,
		BillNumber,
		CompanyId,
		CompanyIdCur,
		DepartmentIdCur,A_tx,l_id,l_type
	)
	SELECT 
		GETDATE(),
		Convert(varchar(10),GETDATE(),120),
		lr.Summary,
		CateOneId,
		CateOneName,
		CateTwoId,
		CateTwoName,
		SubjectId,
		SubjectName,
		@PaymentTypeId,
		@PaymentTypeName,
		@BankId,
		@BankName,
		Reason,
		Prices,
		ENameId,
		EName,
		PZPerson,
		PZPersonName,
		SHPerson,
		SHPersonName,
		@userid,
		@username,
		@Memo,
		lri.Pjbh ,	--'', 票据编号
		@CompanyId,
		@CompanyId,
		@departmentId,lri.A_tx,@pid,'LR'
	FROM L_Reimbursement lr,L_Reimbursement_Item lri WHERE lr.id =lri.Pid AND lri.Pid=@pid

	if @@ROWCOUNT<1
		begin
			set @errorSun=@errorSun+1		
			goto e;
		END

 
                        


DECLARE @ac INT,@i int
SET @ac=ISNULL((SELECT COUNT(1) FROM @TempSca),0) --固定资产总数
SET @i=0
WHILE (@i<@ac)        
BEGIN
	set @i=@i+1
	
	declare @Anumber varchar(10),@tempNumber varchar(10)			
--生成资产编号
			if((select count(id) from Assets)=0)
			set @Anumber = 'LC' + '00000001'
			else
			begin
				set @tempNumber = (select top 1 Anumber from Assets order by convert(integer,substring(Anumber,3,8)) desc)
				set @Anumber = convert(integer,substring(@tempNumber,3,8)) + 1
				if (len(@Anumber)=1)
					set @Anumber = 'LC' +'0000000' + @Anumber
				else if (len(@Anumber)=2)
					set @Anumber = 'LC' +'000000' + @Anumber
				else if (len(@Anumber)=3)
					set @Anumber = 'LC' +'00000' + @Anumber
				else if (len(@Anumber)=4)
					set @Anumber = 'LC' +'0000' + @Anumber
				else if (len(@Anumber)=5)
					set @Anumber = 'LC' +'000' + @Anumber
				else if (len(@Anumber)=6)
					set @Anumber = 'LC' +'00' + @Anumber
				else if (len(@Anumber)=7)
					set @Anumber = 'LC' +'0' + @Anumber
				else
					set @Anumber = 'LC' + @Anumber
			end
	
	INSERT INTO Assets
(	
	GId,
	CategoryId,
	NameId,
	[Description],
	CompanyId,
	DepartmentId,
	UserName,
	EName,
	StateId,
	Prices,
	DJUserName,
	DJDate,
	GGXH,
	GZDate,
	IsApplyBF,
	Memo,
	ApplyBFDate,
	Anumber,
	LyDate,
	oldEName,
	oldUserName,l_id,typeid
)
SELECT	
	lra.GId,
	lra.CategoryId,
	lra.NameId,
	lra.[Description],
	lra.CompanyId,
	lra.DepartmentId,
	lra.UserName,
	lra.EName,
	lra.StateId,
	lra.Prices,
	lra.DJUserName,
	lra.DJDate,
	lra.GGXH,
	lra.GZDate,
	lra.IsApplyBF,
	lra.Memo,
	lra.ApplyBFDate,
	@Anumber,
	lra.LyDate,
	lra.oldEName,
	lra.oldUserName,@pid,typeid
FROM
	L_Reimbursement_Assets lra WHERE lra.id=(SELECT d_str FROM @TempSca WHERE id=@i)
	--PRINT ('insert into [Assets]  '+CONVERT(VARCHAR,@i))
	

	
	if @@ROWCOUNT<1
	begin
		set @errorSun=@errorSun+1		
		goto e;
	END	
END 
	

e:
    PRINT @errorSun
	if @errorSun>0
	begin
		rollback TRANSACTION
	
	end
	else
	begin
		commit TRANSACTION
	END
END	
	RETURN



